Query languages such as Structured Query Language (SQL) are particularly suited for retrieval of data from datastores, regardless of the schema of the data. However, SQL is not suited to data analysis, as it lacks the expressiveness to specify complex, high-level calculations. For example, SQL provides calculation of only one table at a time and lacks constructs such as calculated members, join abstraction, and abstraction of aggregation functions.
In contrast, Multi-Dimensional eXpressions (MDX) is a language providing multi-dimensional analytical queries including calculated measures, calculated members, hierarchical navigation support, and heterogeneous member sets. MDX is therefore commonly used to provide advanced analysis.
MDX, however, requires an underlying multi-dimensional model (i.e., a Cube) which must be authored so as to conform to particular structural requirements. Typically, a Cube represents a set of independent coordinates in an N-dimensional space, each point of which contains a scalar value (i.e., string or numeral). MDX allows programmers to specify sets of coordinates in this space and to retrieve the values corresponding to the coordinates.
Authoring a Cube on top of, for instance, the transactional data model of an application is a relatively costly design task which forces designers to make compromises. For instance, a designer must typically determine a default hierarchy to navigate each dimension of a Cube. Also, all dimensions in a Cube must be orthogonal, so any relations which exist between dimensions in the original schema are lost after the original schema is projected onto a Cube schema.
Data analysis often requires the specification of complex pieces of data, such as an entire report or a dashboard, that can only be materialized in a complex database schema containing, for instance, multiple fact tables sharing some but not all dimension tables. However, SQL, MDX, and other existing query languages for query and analysis are limited in regards to the “shape” of the data sets that a query can return. An SQL SELECT statement, however complex, always computes a single table. Similarly, an MDX statement always brings back a “cube slice” or star schema that includes only one fact table with foreign keys to zero or more independent dimension tables.
Therefore, if a report or dashboard requires more than the above-described simple data topologies, multiple queries to the underlying system must be issued. Then, the client application must reconcile the data returned by the multiple queries, which may involve additional data processing. Consequently, the atomicity of the read transaction is not guaranteed.